package queries;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import entitys.Category;
import entitys.Product;
import entitys.Type;

/**
 * returns list of product categories from ccrm.category
 * @author Dima
 *
 */
public class GetProductCategoryListQuery {

	public static ArrayList<Category> exe(Connection conn) {
		ArrayList<Category> arr = new ArrayList<Category>();
		ResultSet res = null; 
		Statement stmt;
		int index = 0;	
			try {
				stmt = conn.createStatement();
				res = stmt.executeQuery("SELECT * from ccrm.category; ");
				
				while(res.next())
				{
					arr.add(new Category(res.getString(2), res.getInt(1)));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("Error in retrieving categories");
			}
			try {
				res.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		return arr;
		
	}
	
	public static ArrayList<Category> exe(Connection conn,Category category) { // with sector condition
		ArrayList<Category> arr = new ArrayList<Category>();
		ResultSet res = null; 
		PreparedStatement pstmt;

			try {
				pstmt = conn.prepareStatement("SELECT * from ccrm.category where category.name like ?;");
				pstmt.setString(1,"%"+ category.getCategoryName()+"%");
				res = pstmt.executeQuery();
				
				while(res.next())
				{
					arr.add(new Category(res.getString(2), res.getInt(1)));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("Error in retrieving types");
			}
			try {
				res.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		return arr;
		
	}
	
	

}
